package com.cfjjc.gz.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
 

/****
 * 重置密码邮件模板中，需要的指定的动态属性信息。
 * 
 * @author blue
 * 
 */
public class CreateEmailInfoUtil {


	
	public static void createBonus(String toPath,List listdata) {
		
//		Long start=System.currentTimeMillis(),sLong=System.currentTimeMillis(),end;

 
		OutputStream os = null;// 生成的文档
		jxl.write.WritableWorkbook wwb = null;
		jxl.write.WritableSheet ws3 = null;// 建工作 簿
		 
 
		
		System.out.println("start:");
		
		try {
 
			System.out.println("Bonus xls init over");
 		 
			os = new FileOutputStream(new File(toPath));// 生成的文档
			wwb = Workbook.createWorkbook(os);
			ws3 = wwb.createSheet("奖金发放表月报表", 0);// 建工作 簿
			int inputRow=0;
 
 
			int rowNum = listdata.size();// 行数-1
 

			Map<String, String> rMap;
			
 
			jxl.write.WritableFont wfc = new jxl.write.WritableFont(
					WritableFont.ARIAL);
			WritableCellFormat format = new WritableCellFormat(wfc);
			
			format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框

			jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(
					format);
			
			List list1;

			for (int i = 0; i < rowNum; i++) {
				
				list1 = (ArrayList)listdata.get(i);
								
				for (int j = 0; j < 1; j++) {
 
					rMap = new HashMap<String, String>();
					
					for(int k = 0 ;k<list1.size();k++)						
					{
						rMap.put(String.valueOf(k), (String) list1.get(k));						
					}	
					
 
					if (rMap.size()==0) {
						System.out.println("结果中未找到对应值");
					}else {	
								
					 
							makeLabel(rMap, ws3, inputRow++,wcfFC);
		 
					}
  
				}

			}
 
			wwb.write();
 
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				wwb.close();
				os.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	

	/**
	 *通过员工编号列表，获得用户其他属性信息 生成excel。
	 * 
	 * @throws Exception
	 */
	public static void createEmailInfo(String toPath,List listdata,List listdata1) {
		
		Long start=System.currentTimeMillis(),sLong=System.currentTimeMillis(),end;

		// 读取文件的声明
//		UManager um = null;
//		InputStream is = null;
//		jxl.Workbook rwb = null;

		// 写文件声明（有邮箱的）
		OutputStream os = null;// 生成的文档
		jxl.write.WritableWorkbook wwb = null;
		jxl.write.WritableSheet ws = null;// 建工作 簿
		jxl.write.WritableSheet ws1 = null;// 建工作 簿
//		jxl.write.WritableSheet ws2 = null;// 建工作 簿
//		jxl.write.WritableSheet wsNo = null;// 建工作 簿
		
		System.out.println("start:");
		
		try {
//		um = new UManager();
			
			end=System.currentTimeMillis();
			System.out.println("初始化登陆oim用时："+(end-start)/1000.00+"秒");
			start=end;
			
			// 读文件实例化
//			is = new FileInputStream(path);// 创建输入
//			rwb = Workbook.getWorkbook(is);
			
			System.out.println("init over");

			// 写文件实例化(有邮箱的)
			os = new FileOutputStream(new File(toPath));// 生成的文档
			wwb = Workbook.createWorkbook(os);
			ws = wwb.createSheet("用户表", 0);// 建工作 簿
			int inputRow=0;
			
 		//	int inputRowNo=0;
			ws1 = wwb.createSheet("提取奖金表", 1);// 建工作 簿
			
		//	ws2 = wwb.createSheet("用户登录表", 2);// 建工作 簿
 			
			
			
//			Sheet rs = rwb.getSheet(0); // 读取第一个sheet
			// int colNum = rs.getColumns();// 列数
			int rowNum = listdata.size();// 行数-1
		//	int rowNum =12;// 行数-1
		//	String strValue = ""; // 单元格的值

			Map<String, String> rMap;
			
			
			
			// 设置格式
			jxl.write.WritableFont wfc = new jxl.write.WritableFont(
					WritableFont.ARIAL);
			WritableCellFormat format = new WritableCellFormat(wfc);
			
			format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框

			jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(
					format);
			
			List list1;

			for (int i = 0; i < rowNum; i++) {
				
				list1 = (ArrayList)listdata.get(i);
								
				for (int j = 0; j < 1; j++) {
 
					rMap = new HashMap<String, String>();
					
					for(int k = 0 ;k<list1.size();k++)						
					{
						rMap.put(String.valueOf(k), (String) list1.get(k));						
					}	
					
/*					
					rMap.put("2", "221value");
					rMap.put("3", "3311value");*/
					
 
					if (rMap.size()==0) {
						System.out.println("结果中未找到对应值");
					}else {	
								
					//		System.out.println("写入第2个");
							makeLabel(rMap, ws, inputRow++,wcfFC);
		 
					}

				//	strValue = "";
				}

			}
			
			
			
			
			
			
			
			
			
			
			
			int inputRow2=0;
			
//			Sheet rs = rwb.getSheet(0); // 读取第一个sheet
			// int colNum = rs.getColumns();// 列数
			int rowNum2 = listdata1.size();// 行数-1
		//	int rowNum =12;// 行数-1
		//	String strValue = ""; // 单元格的值

			Map<String, String> rMap2;
 
			
			List list2;

			for (int i = 0; i < rowNum2; i++) {
				
				list2 = (ArrayList)listdata1.get(i);
								
				for (int j = 0; j < 1; j++) {
 
					rMap2 = new HashMap<String, String>();
					
					for(int k = 0 ;k<list2.size();k++)						
					{
						rMap2.put(String.valueOf(k), (String) list2.get(k));						
					}	
					
/*					
					rMap.put("2", "221value");
					rMap.put("3", "3311value");*/
					
 
					if (rMap2.size()==0) {
						System.out.println("结果中未找到对应值");
					}else {	
								
					//		System.out.println("写入第2个");
							makeLabel(rMap2, ws1, inputRow2++,wcfFC);
		 
					}

			 
				}

			}
			
			
			
			
			
			
			
			
			
			wwb.write();
 
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				wwb.close();
				os.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	private static void makeLabel(Map<String, String> rMap,
			jxl.write.WritableSheet ws, int rowNum, WritableCellFormat wcfFC) throws Exception {
		jxl.write.Label labelC = null;
		labelC = new jxl.write.Label(0, rowNum, rowNum + 1 + "",wcfFC);
		ws.addCell(labelC);
		int i = 1;
		for (String key : rMap.keySet()) {
			labelC = new jxl.write.Label(i++, rowNum, rMap.get(key),wcfFC);
			ws.addCell(labelC);
		}
	//	labelC = new jxl.write.Label(rMap.size()+1, rowNum, "",wcfFC);
	//	ws.addCell(labelC);
	}

	/**
	 * 填充excel中sheet页
	 * 
	 * @param ws
	 * @param sqlString
	 * @param lieNames
	 *            每列的列名
	 * @throws Exception
	 */
/*	private static void makeTitle(jxl.write.WritableSheet ws,
			List<String> lieNames) throws Exception {

		jxl.write.Label labelC = null;

		// 设置格式
		jxl.write.WritableFont wfc = new jxl.write.WritableFont(
				WritableFont.ARIAL);
		WritableCellFormat format = new WritableCellFormat(wfc);
		
		format.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);// 黑色边框

		jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(
				format);

		// 填充标题
		int lieindex = 0;
		for (String lie : lieNames) {
			labelC = new jxl.write.Label(lieindex++, 0, lie, wcfFC);
			ws.addCell(labelC);
		}

	}*/

	public static void main(String[] args) {
		
		List haha = new ArrayList();
		List haha1 = new ArrayList();
		List haha2 = new ArrayList();
		
		haha1.add("111111");
		haha1.add("222222");
		haha1.add("333333");
		haha1.add("444444");
		haha1.add("555555");
		
		
		haha2.add("11111");
		haha2.add("22222");
		haha2.add("33333");
		haha2.add("44444");
		haha2.add("55555");
		
		haha.add(haha1);
		haha.add(haha2);
		
			/*createEmailInfo("F:/empno.xls", "F:/accouts.xls");*/
		createEmailInfo("C:\\2013VW\\test.xls",haha,haha);
		
	}

}
